<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title>Web高级开发 | Advanced Web Development Course</title>
    <link
      href="https://fonts.googleapis.com/css?family=Heebo:300,400"
      rel="stylesheet"
    />
    <link rel="stylesheet" href="../../css/main.css" />
    <link rel="stylesheet" href="../../css/tabs.css" />
    <script src="../../js/uikit.js"></script>
    <link href="../../css/prism.css" rel="stylesheet" />
    <script src="../../js/prism.js"></script>
  </head>

  <body>
    <div
      data-uk-sticky="animation: uk-animation-slide-top; sel-target: .uk-navbar-container; cls-active: uk-navbar-sticky; cls-inactive: uk-navbar-transparent; top: 300"
    >
      <nav class="uk-navbar-container pt-2 pb-2">
        <div class="uk-container">
          <div data-uk-navbar>
            <div class="uk-navbar-left">
              <a
                class="uk-navbar-item uk-logo uk-visible@m"
                href="../../index.html"
                >Web高级开发课程</a
              >
              <a
                class="uk-navbar-tuk-containeroggle uk-hidden@m"
                href="#offcanvas-docs"
                data-uk-toggle
                ><span data-uk-navbar-toggle-icon></span>
                <span class="uk-margin-small-left">目录</span></a
              >
              <ul class="uk-navbar-nav uk-visible@m">
                <li><a href="../../index.html">文档</a></li>
              </ul>
            </div>
            <div class="uk-navbar-center uk-hidden@m">
              <a class="uk-navbar-item uk-logo" href="../../index.html"
                >Web高级开发课程</a
              >
            </div>
          </div>
        </div>
      </nav>
    </div>

    <div class="uk-section">
      <div class="uk-container">
        <div class="uk-grid-large" data-uk-grid>
          <div class="sidebar-fixed-width uk-visible@m">
            <div class="sidebar-docs uk-position-fixed uk-margin-top">
              <h5>关于这门课</h5>
              <ul class="uk-nav uk-nav-default doc-nav">
                <li><a href="../../index.html">大纲</a></li>
              </ul>
              <h5>章节</h5>
              <ul class="uk-nav uk-nav-default doc-nav">
                <li><a href="../01.html">第1章 - Spring基础</a></li>
                <li><a href="../02.html">第2章 - Spring中的Bean</a></li>
                <li><a href="../03.html">第3章 - Spring AOP</a></li>
                <li><a href="../04.html">第4章 - Spring的事物管理</a></li>
                <li><a href="../05.html">第5章 - 初识MyBatis</a></li>
                <li><a href="../06.html">第6章 - MyBatis的核心配置</a></li>
                <li><a href="../07.html">第7章 - MyBatis的高级应用</a></li>
                <li><a href="../08.html">第8章 - Spring MVC入门</a></li>
                <li><a href="../09.html">第9章 - Spring MVC数据绑定</a></li>
                <li><a href="../10.html">第10章 - Spring MVC拦截器</a></li>
                <li><a href="../11.html">第11章 - Spring Boot基础</a></li>
                <li><a href="../12.html">第12章 - Spring Boot的Web开发</a></li>
                <li>
                  <a href="../13.html">第13章 - Spring Boot的数据库连接</a>
                </li>
                <li><a href="../14.html">第14章 - 框架整合</a></li>
              </ul>
              <h5>实验课</h5>
              <ul class="uk-nav uk-nav-default doc-nav">
                <li><a href="./code-01.html">01</a></li>
                <li><a href="./code-02.html">02</a></li>
                <li><a href="./code-03.html">03</a></li>
                <li><a href="./code-04.html">04</a></li>
                <li class="uk-active"><a href="./code-05.html">05</a></li>
                <li><a href="./code-06.html">06</a></li>
                <li><a href="./code-07.html">07</a></li>
                <li><a href="./code-08.html">08</a></li>
                <li><a href="./code-09.html">09</a></li>
                <li><a href="./code-10.html">10</a></li>
                <li><a href="./code-11.html">11</a></li>
                <li><a href="./code-12.html">12</a></li>
              </ul>
            </div>
          </div>
          <div class="uk-width-1-1 uk-width-expand@m">
            <article class="uk-article">
              <h3>实验课05</h3>
              <div>
                






<div class="tabs">
  <div class="tab-3">
		<label for="tab3-1">机房的数据库</label>
		<input id="tab3-1" name="tabs-three" type="radio" checked="checked"/>
		<div>
      <p>
        0.先对数据库操作一波
        <br><br>
        登录<br><br>
        <img src="../../images/lab05/login.jpg" alt=""><br><br>
        创建一个数据库，叫lab05<br><br>
        <img src="../../images/lab05/111.jpg" alt=""><br><br>
        <img src="../../images/lab05/222.jpg" alt=""><br><br>
        在lab05里建一个t_user表(起名字叫t_user,建好后它会自动变成途中的dbo.t_user,可以忽略这个dbo_)<br><br>
        <img src="../../images/lab05/333.jpg" alt=""><br><br>
        然后对它进行编辑<br><br>
        <img src="../../images/lab05/444.jpg" alt=""><br><br>
      </p>
    </div>
  </div>
	<div class="tab-3">
		<label for="tab3-2">动态SQL</label>
		<input id="tab3-2" name="tabs-three" type="radio"/>
		<div>
      <p>请先在这里下载基本代码：<a href="../../files/lab05.zip" download>Lab05的代码</a></p>
      <p>解压后，用SpringToolSuite(Eclipse)或者Idea导入，这里以SpringToolSuite为例</p>
      <p>右键，然后选择import...</p>
      <img src="../../images/lab05/im.JPG" alt="">
      <p>右键，然后选择import...</p>
      <img src="../../images/lab05/import.JPG" alt="">
      <p>再选择Maven-->Existing Maven Projects, 再选择解压好的lab05即可。</p>

      <p>
        1. t_user的数据库长成这样
        <br><br>
        <img src="../../images/lab05/t_user.JPG" alt="t_user">
        <br><br>
        2. 现在要通过名字和职业查找用户，用上周的方式是
      </p>
      <p>在UserMapper.xml里的&lt;mapper&gt;元素中加入：</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByNameAndJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
  select * from t_user where username=#{username} and job=#{job}
&lt;/select&gt;</code></pre>
<p>于是你现在的UserMapper.xml大约长这个样子</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.UserMapper"&gt;
  &lt;!-- TODO: 添加配置内容 --&gt;
  &lt;select id="findUserByNameAndJob"
    parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
    select * from t_user where username=#{username} and job=#{job}
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>
<p>在SQLMain.java的main方法中加入:</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
// ============= &lt;if&gt; + &lt;where&gt; / &lt;trim&gt; =============
// 创建User对象，封装需要组合查询的条件
User user=new User();
user.setUsername("zhangsan");
user.setJob("player");
//执行sqlSession的查询方法，返回结果集
List&lt;User&gt; users = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameAndJob", user);
for (User u : users) {
System.out.println(u.toString());
}
sqlSession.close();</code></pre>
<p>运行，可以打印出zhangsan的信息</p>
<p>
但是如果只设置了username而忘了设置job
<br>
<pre class="line-numbers"><code class="language-java">user.setUsername("zhangsan");
// user.setJob("player");</code></pre>
<p>再打印就没有结果了：</p>
<img src="../../images/lab05/what.JPG" alt="what">
</p>
<p>于是使用&lt;if&gt;就可以解决这个问题<br>
请将第2步中UserMapper.xml的&lt;select&gt;的部分替换成下面的代码到：
</p>

<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByNameAndJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
  select * from t_user where
    &lt;if test="username != null and username !=''"&gt;
      username=#{username}  
    &lt;/if&gt;
    &lt;if test="job != null and job !=''"&gt;
      and job=#{job}
    &lt;/if&gt;
&lt;/select&gt;</code></pre>

<p>于是你现在的UserMapper.xml大约长这个样子</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.UserMapper"&gt;
  &lt;!-- TODO: 添加配置内容 --&gt;
  &lt;select id="findUserByNameAndJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
    select * from t_user where
      &lt;if test="username != null and username !=''"&gt;
        username=#{username}
      &lt;/if&gt;
      &lt;if test="job != null and job !=''"&gt;
        and job=#{job}
      &lt;/if&gt;
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>









<p>3. 但是问题来了，如果这个时候设置了job但是没有设置username，就会报错了，
因为在没有设置username的时候，SQL语句就变成了 <strong>select * from t_user <span style="color:red">where and </span>job=#{job}</strong> 。
于是在上面的基础上，可以再加个&lt;where&gt;</p>
<p>请把上一步中&lt;select&gt;元素的部分替换成下面的代码</p>
<pre class="line-numsbers"><code class="language-xml">&lt;select id="findUserByNameAndJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
  select * from t_user 
  &lt;where&gt;
    &lt;if test="username != null and username !=''"&gt;
      and username=#{username}  
    &lt;/if&gt;
    &lt;if test="job != null and job !=''"&gt;
      and job=#{job}
    &lt;/if&gt;
  &lt;/where&gt; 
&lt;/select&gt;</code></pre>

<p>除此之外，还可以用&lt;trim&gt;的方式</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByNameAndJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
  select * from t_user 
    &lt;trim prefix="where" prefixOverrides="and"&gt;
      &lt;if test="username != null and username !=''"&gt;
        and username=#{username}  
      &lt;/if&gt;
      &lt;if test="job != null and job !=''"&gt;
        and job=#{job}
      &lt;/if&gt;
    &lt;/trim&gt;
&lt;/select&gt;</code></pre>
<p>它和上面&lt;where&gt;的方式所达到的效果是一样的</p>

<p>4. 但是，在实际情况中，有的时候需要从多个选项中选择一个执行，比如
<br>
如果用户名不为空，那么就用用户名进行筛选，
<br>
如果用户名为空, 职业不为空，那么就用职业进行筛选，
<br>
如果用户名和职业都为空, 那么就要查出所有电话不为空的用户信息。
<br>
于是可以用<strong>&lt;choose&gt;&lt;when&gt;</strong>和<strong>&lt;otherwise&gt;</strong>的组合
<br>
请把下面的代码加到UserMapper.xml的&lt;mapper&gt;元素中
</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByNameOrJob" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
select * from t_user where 1 = 1
  &lt;choose&gt;
    &lt;when test="username !=null and username !=''"&gt;
      and username like concat('%',#{username},'%')
    &lt;/when&gt;
    &lt;when test="job !=null and job !=''"&gt;
      and job=#{job}
    &lt;/when&gt;
    &lt;otherwise&gt;
      and phone is not null
    &lt;/otherwise&gt;
  &lt;/choose&gt;
&lt;/select&gt;</code></pre>
然后SQLMain.java的main方法中替换成：
<pre class="line-numbers"><code class="language-java">User user=new User();
user.setUsername("zhangsan");
user.setJob("player");
List&lt;User&gt; users = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameOrJob", user);
for (User u : users) {
System.out.println(u.toString());
}
sqlSession.close();</code></pre>

<p>此时，你的UserMapper.xml大约是这个亚子：</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.UserMapper"&gt;
  &lt;!-- TODO: 添加配置内容 --&gt;
  &lt;select id="findUserByNameAndJob"
    parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
    select * from t_user
    &lt;where&gt;
      &lt;if test="username != null and username !=''"&gt;
        and username=#{username}
      &lt;/if&gt;
      &lt;if test="job != null and job !=''"&gt;
        and job=#{job}
      &lt;/if&gt;
    &lt;/where&gt;
  &lt;/select&gt;

  &lt;select id="findUserByNameOrJob" parameterType="com.ssm.po.User"
    resultType="com.ssm.po.User"&gt;
    select * from t_user where 1 = 1
    &lt;choose&gt;
      &lt;when test="username !=null and username !=''"&gt;
        and username like concat('%',#{username},'%')
      &lt;/when&gt;
      &lt;when test="job !=null and job !=''"&gt;
        and job=#{job}
      &lt;/when&gt;
      &lt;otherwise&gt;
        and phone is not null
      &lt;/otherwise&gt;
    &lt;/choose&gt;
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>
<p>SQLMain.java的main方法大约长这样：</p>
<pre class="line-numbers"><code class="language-java">public static void main(String[] args) {
  SqlSession sqlSession = MybatisUtils.getSession();

  User user=new User();
  user.setUsername("zhangsan");
  user.setJob("player");
  List<User> users = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameOrJob", user);
  for (User u : users) {
  System.out.println(u.toString());
  }
  sqlSession.close();
}
</code></pre>


<p><strong>&lt;choose&gt;&lt;when&gt;</strong>和<strong>&lt;otherwise&gt;</strong>就类似Java里的switch...case...default</p>

<p>5. 同样的，对于更新信息，之前的做法是</p>
<pre class="line-numbers"><code class="language-xml">&lt;update id="updateUser" parameterType="com.ssm.po.User"&gt;
  update t_user set
  username=#{username},job=#{job},phone=#{phone} where id=#{id}
&lt;/update&gt;</code></pre>
<p>但是依然如果某个值忘了设置，上面的SQL语句就会有问题，于是可以利用&lt;set&gt;</p>
<pre class="line-numbers"><code class="language-xml">&lt;update id="updateUser" parameterType="com.ssm.po.User"&gt;
  update t_user
  &lt;set&gt;
    &lt;if test="username !=null and username !=''"&gt;
      username=#{username},
    &lt;/if&gt;
    &lt;if test="job !=null and job !=''"&gt;
      job=#{job},
    &lt;/if&gt;
    &lt;if test="phone !=null and phone !=''"&gt;
      phone=#{phone},
    &lt;/if&gt;
  &lt;/set&gt;
  where id=#{id}
&lt;/update&gt;</code></pre>
<p>在main方法替换成下面的代码：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
User user = new User();
user.setId(2);
user.setUsername("lisi");
user.setJob("cike");
user.setPhone("13624589654");
int rows = sqlSession.update("com.ssm.mapper.UserMapper.updateUser", user);
if (rows > 0) {
System.out.println("成功修改了" + rows + "条数据！");
} else {
System.out.println("修改数据失败!");
}
sqlSession.commit();
sqlSession.close();</code></pre>
<p>此时，lisi的职业就变成了刺客
  <br>
  <img src="../../images/lab05/lisi.JPG" alt="lisi-cike">
</p>



<p>6. 如果有1000个用户，现在需要挑选出其中100个符合要求的用户，就可以使用<strong>&lt;foreach&gt;</strong>来遍历这100个用户</p>
<p>请把下面的代码加到UserMapper.xml的&lt;mapper&gt;元素中</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByIds" parameterType="List" resultType="com.ssm.po.User"&gt;
  select * from t_user where id in
  &lt;foreach item="id" index="index" collection="list" open="(" separator="," close=")"&gt;
    #{id}
  &lt;/foreach&gt;
&lt;/select&gt;</code></pre>
<p>在main方法里替换成：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
List&lt;Integer&gt; ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
List&lt;User&gt; users = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByIds", ids);
for (User user : users) {
System.out.println(user.toString());
}
sqlSession.close();</code></pre>



<p>7. 为了兼容不同数据库和防止被黑(SQL注入)</p>
<p>请把下面的代码加到UserMapper.xml的&lt;mapper&gt;元素中 (这波SQL语句是查找名字里带有's'的兄弟)</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findUserByNameBind" parameterType="com.ssm.po.User" resultType="com.ssm.po.User"&gt;
  &lt;!--_parameter.getUsername()也可以直接写成传入的字段属性名，即username --&gt;
  &lt;bind name="p_username" value="'%'+_parameter.getUsername()+'%'" /&gt;
  select * from t_user where username like #{p_username}
&lt;/select&gt;</code></pre>
<p>在main方法替换成：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
User user=new User();
user.setUsername("s");
List &lt;User&gt; users = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameBind", user);
for (User u : users) {
System.out.println(u.toString());
}
sqlSession.close();	</code></pre>
		</div>
	</div>	
	<div class="tab-3">
		<label for="tab3-3">MyBatis关联映射</label>
		<input id="tab3-3" name="tabs-three" type="radio" />
		<div>
      <p>1. 需要的数据表
        <br><br>
        tb_student:
        <br><br>
        <img src="../../images/lab05/student.JPG" alt="student">
        <br><br>
        tb_studentidcard:
        <br><br>
        <img src="../../images/lab05/idcard.JPG" alt="student">
      </p>
      <p>2. 1对1</p>
      <p>StudentCardMapper.xml</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.StudentIdCardMapper"&gt;
  &lt;!--根据id获取学生证信息 --&gt;
  &lt;select id="findStudentIdCardById" parameterType="Integer" resultType="StudentIdCard"&gt;
    select * from tb_studentidcard where id=#{id}
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>
<p>StudentMapper.xml
<br>
如果SQL有段位，青铜的这样写：
</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.StudentMapper"&gt;
  &lt;select id="findStudentById" parameterType="Integer" resultType="Student"&gt;
    select * from tb_student where id=#{id}
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>
<p>为了把student id card也整合进去，就需要用到<strong>&lt;resultMap&gt;</strong>和<strong>&lt;association&gt;</strong>
<br>
钻石段位的这样写，把上面代码中的&lt;select&gt;...&lt;/select&gt;的部分替换成下面的代码
</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findStudentById2" parameterType="Integer" resultMap="StudentIdCardWithStudentResult2"&gt;
  select s.*,sidcard.code 
  from tb_student s,tb_studentidcard sidcard
   where s.card_id=sidcard.id and s.id=#{id}
&lt;/select&gt;
&lt;resultMap type="Student" id="StudentIdCardWithStudentResult2"&gt;
  &lt;id property="id" column="id" /&gt;
  &lt;result property="name" column="name" /&gt;
  &lt;result property="sex" column="sex" /&gt;
  &lt;association property="studentIdCard" javaType="StudentIdCard"&gt;
    &lt;id property="id" column="card_id" /&gt;
    &lt;result property="code" column="code" /&gt;
  &lt;/association&gt;
&lt;/resultMap&gt;</code></pre>
<p>的AssociationMain.java的main方法中写入：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
Student student = sqlSession.selectOne("com.ssm.mapper.StudentMapper.findStudentById2", 1);
System.out.println(student.toString());
sqlSession.close();</code></pre>

<p>3. 1对多
  <br><br>
  加个tb_banji表:<br><br>
  <img src="../../images/lab05/banji.JPG" alt="">
  <br><br>
  BanjiMapper.xml
  <br>
</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.BanjiMapper"&gt;
  &lt;select id="findBanjiWithStudent" parameterType="Integer" resultType="com.ssm.po.Banji"&gt; 
    select b.*, s.id as student_id,s.name as student_name
    from tb_banji b,tb_student s where 
    b.id=s.banji_id and b.id=#{id} 
  &lt;/select&gt;
&lt;/mapper&gt;</code></pre>
<p>同样的上面的mapper依然不能显示出student list，
  <br>
  把上面代码中的&lt;select&gt;...&lt;/select&gt;的部分替换成下面的代码
</p>
<pre class="line-numbers"><code class="language-xml">&lt;select id="findBanjiWithStudent2" parameterType="Integer" resultMap="BanjiWithStudentResult"&gt;
  select b.*, s.id as student_id,s.name as student_name
  from tb_banji b,tb_student s
  where b.id=s.banji_id and b.id=#{id}
&lt;/select&gt;
&lt;resultMap id="BanjiWithStudentResult" type="Banji"&gt;
  &lt;id column="id" property="id" /&gt; &lt;!-- 主键  --&gt;
  &lt;result column="name" property="name" /&gt;
  &lt;!--一对多关联映射：collectionofType表示属性集合中元素的类型List&lt;Student&gt;属性即Student类 --&gt;
  &lt;collection property="studentList" ofType="Student"&gt;
    &lt;id column="student_id" property="id" /&gt;
    &lt;result column="student_name" property="name" /&gt;
    &lt;result column="sex" property="sex" /&gt;
  &lt;/collection&gt;
&lt;/resultMap&gt;</code></pre>
<p>将AssociationMain.java中main方法里的代码替换为下面的代码：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
Banji banji=sqlSession.selectOne("com.ssm.mapper.BanjiMapper.findBanjiWithStudent2",1);
System.out.println(banji.toString());
sqlSession.close();	</code></pre>
<p>再运行会得到：
  <br>
  Banji [id=1, name=一班, studentList=[Student [id=1, name=LiLei, sex=m, studentIdCard= null]]]
</p>

<p>3. 多对多
  <br>
  <img src="../../images/lab05/many-to-many.png" alt="many-to-many">
</p>
<p>
再实际开发中，多对多的关联关系非常常见，比如一个学生可以选择多门课程，而一门课程又可以被多个学生选择，学生和课之间就属于多对多的关联关系。
<br>
不过多对多的关联关系通常用一个中间表来维护，比如这里的中间表选课表中的学生id(student_id)作为外键参照学生的id，对于studentiId来说是(主键)，课程id(course_id)作为外键参照课程表的id。
</p>
<p>tb_course:</p>
<img src="../../images/lab05/course.JPG" alt="course">
<p>tb_elective_course:</p>
<img src="../../images/lab05/elective_course.JPG" alt="elective_course">

<p>CourseMapper.xml</p>
<pre class="line-numbers"><code class="language-xml">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
&lt;mapper namespace="com.ssm.mapper.CourseMapper"&gt;
  &lt;!--多对多嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型  --&gt;
  &lt;select id="findCourseWithStudent" parameterType="Integer"
    resultMap="CourseWithStudentResult"&gt;
    select * from tb_course where id=#{id}
  &lt;/select&gt;
  &lt;resultMap type="Course" id="CourseWithStudentResult"&gt;
    &lt;id property="id" column="id" /&gt;
    &lt;result property="name" column="name" /&gt;
    &lt;result property="code" column="code" /&gt;
    &lt;collection property="studentList" column="id"
			ofType="Student"
			select="com.ssm.mapper.StudentMapper.findStudentByIdWithElectiveCourse"&gt;
		&lt;/collection&gt;
  &lt;/resultMap&gt;
&lt;/mapper&gt;</code></pre>


<p>StudentMapper.xml
<br>
添加以下代码到StudentMapper.xml中（放到&lt;mapper&gt;元素中）
</p>

<pre class="line-numbers"><code class="language-xml">&lt;select id="findStudentByIdWithElectiveCourse" parameterType="Integer" resultType="student"&gt;
  select * from tb_student where id in 
  (select student_id from tb_elective_course where course_id=#{id})
&lt;/select&gt;</code></pre>



<p>将AssociationMain.java中main方法里的代码替换为下面的代码：</p>
<pre class="line-numbers"><code class="language-java">SqlSession sqlSession = MybatisUtils.getSession();
//查询id为1的课程中的学生信息
Course course=sqlSession.selectOne("com.ssm.mapper.CourseMapper.findCourseWithStudent", 1);
System.out.println(course);
sqlSession.close();	</code></pre>
<p>再运行会得到：
  <br>
  Course [id=1, name=Java, code=101, studentList=[Student [id=1, name=LiLei, sex=m, studentIdCard= null]]]
</p>













		</div>
	</div>
</div>








              </div>
            </article>
          </div>
        </div>
      </div>
    </div>




    <div id="offcanvas-docs" data-uk-offcanvas="overlay: true">
      <div class="uk-offcanvas-bar">
        <button class="uk-offcanvas-close" type="button" data-uk-close></button>
        <h5 class="uk-margin-top">关于这门课</h5>
        <ul class="uk-nav uk-nav-default doc-nav">
          <li><a href="../indext.html">大纲</a></li>
        </ul>
        <h5 class="uk-margin-top">章节</h5>
        <ul class="uk-nav uk-nav-default doc-nav">
          <li><a href="../01.html">第1章 - Spring基础</a></li>
          <li><a href="../02.html">第2章 - Spring中的Bean</a></li>
          <li><a href="../03.html">第3章 - Spring AOP</a></li>
          <li><a href="../04.html">第4章 - Spring的事物管理</a></li>
          <li><a href="../05.html">第5章 - 初识MyBatis</a></li>
          <li><a href="../06.html">第6章 - MyBatis的核心配置</a></li>
          <li><a href="../07.html">第7章 - MyBatis的高级应用</a></li>
          <li><a href="../08.html">第8章 - Spring MVC入门</a></li>
          <li><a href="../09.html">第9章 - Spring MVC数据绑定</a></li>
          <li><a href="../10.html">第10章 - Spring MVC拦截器</a></li>
          <li><a href="../11.html">第11章 - Spring Boot基础</a></li>
          <li><a href="../12.html">第12章 - Spring Boot的Web开发</a></li>
          <li><a href="../13.html">第13章 - Spring Boot的数据库连接</a></li>
          <li><a href="../14.html">第14章 - 框架整合</a></li>
        </ul>
        <h5>实验课</h5>
        <ul class="uk-nav uk-nav-default doc-nav">
          <li><a href="./code-01.html">01</a></li>
          <li><a href="./code-02.html">02</a></li>
          <li><a href="./code-03.html">03</a></li>
          <li><a href="./code-04.html">04</a></li>
          <li class="uk-active"><a href="./code-05.html">05</a></li>
          <li><a href="./code-06.html">06</a></li>
          <li><a href="./code-07.html">07</a></li>
          <li><a href="./code-08.html">08</a></li>
          <li><a href="./code-09.html">09</a></li>
          <li><a href="./code-10.html">10</a></li>
          <li><a href="./code-11.html">11</a></li>
          <li><a href="./code-12.html">12</a></li>
        </ul>
      </div>
    </div>

    <footer class="uk-section uk-text-center uk-text-muted">
      <div class="uk-container uk-container-small">
        <div class="uk-margin-medium"></div>
      </div>
    </footer>

    <script src="../js/awesomplete.js"></script>
    <script src="../js/custom.js"></script>
  </body>
</html>
